<?php
 
 include_once "../lib/DAL/LopMon.php";

 class LopMonBLL{
    
    function __construct(){
        
    }
    
    function __destruct(){
        
    }
    
    public function getLopMonInfo($ma_lopmon){
        $sql ="select * from tbl_lopmon where ma_lopmon ='".$ma_lopmon."'";        
        $result =mysql_fetch_array( mysql_query($sql) or die(mysql_error()));
        $this->setLopMonBLL($result);
        
    }
    
    public function getLich_Donvi_HocVu_CB($donvi,$hocvu,$ma_cb){
            $sql="  SELECT  DISTINCT  tbl_lopmon.*,ten_mh,khungthoigian,khoiluong,ten_donvi
                    FROM  tbl_ctdt,tbl_mon_ctdt,dm_monhoc,tbl_lopmon_monctdt,tbl_lopmon,dm_cahoc,tbl_donvi 
                    WHERE tbl_ctdt.ma_ctdt=tbl_mon_ctdt.ma_ctdt
                    AND   tbl_lopmon.ma_donvi=tbl_donvi.ma_donvi  
                    AND   tbl_mon_ctdt.ma_monhoc=dm_monhoc.madm_mh
                    AND   tbl_mon_ctdt.ma_mon_ctdt=tbl_lopmon_monctdt.ma_mon_ctdt
                    AND   tbl_lopmon.ma_lopmon = tbl_lopmon_monctdt.ma_lopmon
                    AND   tbl_lopmon.madm_ca = dm_cahoc.madm_ca
                    AND   ma_donvihocvu='$hocvu'
                    AND   tbl_lopmon.ma_lopmon NOT IN (SELECT ma_lopmon FROM tbl_laplich WHERE ma_cb='$ma_cb')";
                    
        if($donvi!=""){
            $sql.=" AND   tbl_donvi.ma_donvi='$donvi'";
        }
         
        $result = mysql_query($sql) or die(mysql_error());
        $arr= array();
        while($row = mysql_fetch_array($result)){
            $arr[] = $row;
        }
        return $arr;
        
    }
    
     public function getLich_Donvi_HocVu($donvi,$hocvu){
            $sql="  SELECT  DISTINCT  tbl_lopmon.*,ten_mh,khungthoigian,khoiluong,ten_donvi
                    FROM  tbl_ctdt,tbl_mon_ctdt,dm_monhoc,tbl_lopmon_monctdt,tbl_lopmon,dm_cahoc,tbl_donvi 
                    WHERE tbl_ctdt.ma_ctdt=tbl_mon_ctdt.ma_ctdt
                    AND   tbl_lopmon.ma_donvi=tbl_donvi.ma_donvi  
                    AND   tbl_mon_ctdt.ma_monhoc=dm_monhoc.madm_mh
                    AND   tbl_mon_ctdt.ma_mon_ctdt=tbl_lopmon_monctdt.ma_mon_ctdt
                    AND   tbl_lopmon.ma_lopmon = tbl_lopmon_monctdt.ma_lopmon
                    AND   tbl_lopmon.madm_ca = dm_cahoc.madm_ca
                    AND   ma_donvihocvu='$hocvu'";
        if($donvi!=""){
            $sql.=" AND   tbl_donvi.ma_donvi='$donvi'";
        }
                    
        $result = mysql_query($sql) or die(mysql_error());
        $arr= array();
        while($row = mysql_fetch_array($result)){
            $arr[] = $row;
        }
        return $arr;
        
    }
    public function getLich_Donvi_HocVu_Lophc($donvi,$hocvu,$lophc){
            $sql="  SELECT  DISTINCT  tbl_lopmon.*,ten_mh,khungthoigian,khoiluong
                    FROM  tbl_ctdt,tbl_mon_ctdt,dm_monhoc,tbl_lopmon_monctdt,tbl_lopmon,dm_cahoc,tbl_quatrinhlopmon 
                    WHERE tbl_ctdt.ma_ctdt=tbl_mon_ctdt.ma_ctdt
                    AND   tbl_mon_ctdt.ma_monhoc=dm_monhoc.madm_mh
                    AND   tbl_mon_ctdt.ma_mon_ctdt=tbl_lopmon_monctdt.ma_mon_ctdt
                    AND   tbl_lopmon.ma_lopmon = tbl_lopmon_monctdt.ma_lopmon
                    AND   tbl_lopmon.madm_ca = dm_cahoc.madm_ca
                    AND   tbl_quatrinhlopmon.ma_lopmon=tbl_lopmon.ma_lopmon
                    AND   ma_donvi='$donvi'
                    AND   ma_donvihocvu='$hocvu'
                    AND   noidungthuchien='$lophc'";
                    
        $result = mysql_query($sql) or die(mysql_error());
        $arr= array();
        while($row = mysql_fetch_array($result)){
            $arr[] = $row;
        }
        return $arr;
        
    }
    public function getLich_ByNhomLich($lich){
        $sql="  SELECT  DISTINCT  tbl_lopmon.*,ten_mh,khungthoigian,khoiluong,hoten_cb,ten_ttlm
                FROM  tbl_ctdt,tbl_mon_ctdt,dm_monhoc,tbl_lopmon_monctdt,tbl_lopmon,dm_cahoc,tbl_laplich,tbl_canbo,dm_trangthai_lopmon 
                WHERE tbl_ctdt.ma_ctdt=tbl_mon_ctdt.ma_ctdt
                AND   tbl_mon_ctdt.ma_monhoc=dm_monhoc.madm_mh
                AND   tbl_mon_ctdt.ma_mon_ctdt=tbl_lopmon_monctdt.ma_mon_ctdt
                AND   tbl_lopmon.ma_lopmon = tbl_lopmon_monctdt.ma_lopmon
                AND   tbl_lopmon.madm_ca = dm_cahoc.madm_ca
                AND   tbl_laplich.ma_lopmon=tbl_lopmon.ma_lopmon
                AND   tbl_canbo.ma_cb=tbl_lopmon.ma_cbgv  
                AND   tbl_lopmon.trangthai_hientai=dm_trangthai_lopmon.madm_ttlm  
                AND   nhomlich_laplich='$lich'";
        $result = mysql_query($sql) or die(mysql_error());
        $arr= array();
        while($row = mysql_fetch_array($result)){
            $arr[] = $row;
        }
        return $arr;
        
    }
    
    public function getlopmon_donvi_diemdk($donvi,$hocvu,$lophc){
            $sql="  SELECT  DISTINCT  tbl_lopmon.*,ten_mh,khungthoigian,khoiluong
                    FROM  tbl_ctdt,tbl_mon_ctdt,dm_monhoc,tbl_lopmon_monctdt,tbl_lopmon,dm_cahoc,tbl_quatrinhlopmon 
                    WHERE tbl_ctdt.ma_ctdt=tbl_mon_ctdt.ma_ctdt
                    AND   tbl_mon_ctdt.ma_monhoc=dm_monhoc.madm_mh
                    AND   tbl_mon_ctdt.ma_mon_ctdt=tbl_lopmon_monctdt.ma_mon_ctdt
                    AND   tbl_lopmon.ma_lopmon = tbl_lopmon_monctdt.ma_lopmon
                    AND   tbl_lopmon.madm_ca = dm_cahoc.madm_ca
                    AND   tbl_quatrinhlopmon.ma_lopmon=tbl_lopmon.ma_lopmon
                    AND   ma_donvi='$donvi'
                    AND   ma_donvihocvu='$hocvu'
                    AND   ngaybatdau!=''";
           if($lophc!=""){
            $sql.=" AND   noidungthuchien='$lophc'";
           }         
            
                    
        $result = mysql_query($sql) or die(mysql_error());
        $arr= array();
        while($row = mysql_fetch_array($result)){
            $arr[] = $row;
        }
        return $arr;
        
    }
    
    public function getLich_ByGiangVien($ma_cb){
        $sql="  SELECT  DISTINCT  tbl_lopmon.*,ten_mh,hoten_cb,ten_ttlm,ten_donvi
                FROM  tbl_ctdt,tbl_mon_ctdt,dm_monhoc,tbl_lopmon_monctdt,tbl_lopmon,tbl_canbo,dm_trangthai_lopmon,tbl_donvi 
                WHERE tbl_ctdt.ma_ctdt=tbl_mon_ctdt.ma_ctdt
                AND   tbl_mon_ctdt.ma_monhoc=dm_monhoc.madm_mh
                AND   tbl_donvi.ma_donvi=tbl_lopmon.ma_donvi  
                AND   tbl_mon_ctdt.ma_mon_ctdt=tbl_lopmon_monctdt.ma_mon_ctdt
                AND   tbl_lopmon.ma_lopmon = tbl_lopmon_monctdt.ma_lopmon
                AND   tbl_canbo.ma_cb=tbl_lopmon.ma_cbgv  
                AND   tbl_lopmon.trangthai_hientai=dm_trangthai_lopmon.madm_ttlm  
                AND   tbl_lopmon.ma_cbgv='$ma_cb'";
        $result = mysql_query($sql) or die(mysql_error());
        $arr= array();
        while($row = mysql_fetch_array($result)){
            $arr[] = $row;
        }
        return $arr;
        
    }
    
    public function get_SumLich_ByGiangVien(){
        $sql="  SELECT  DISTINCT  tbl_canbo.*,count(ma_lopmon) as tong
                FROM  tbl_canbo,tbl_lopmon
                WHERE tbl_canbo.ma_cb=tbl_lopmon.ma_cbgv  
                GROUP BY tbl_canbo.ma_cb";
         
        $result = mysql_query($sql) or die(mysql_error());
        $arr= array();
        while($row = mysql_fetch_array($result)){
            $arr[] = $row;
        }
        return $arr;
        
    }
    
     public function getnganh_lopmon($donvi,$hocvu){
            $sql="  SELECT DISTINCT tbl_lopmon.ma_lopmon,tbl_ctdt.madm_nganh,ten_nganh
                    FROM  tbl_ctdt,tbl_mon_ctdt,dm_nganh,tbl_lopmon_monctdt,tbl_lopmon 
                    WHERE tbl_ctdt.ma_ctdt=tbl_mon_ctdt.ma_ctdt
                    AND   dm_nganh.madm_nganh=tbl_ctdt.madm_nganh
                    AND   tbl_mon_ctdt.ma_mon_ctdt=tbl_lopmon_monctdt.ma_mon_ctdt
                    AND   tbl_lopmon.ma_lopmon = tbl_lopmon_monctdt.ma_lopmon
                    AND   ma_donvi='$donvi'
                    AND   ma_donvihocvu='$hocvu'";
        $result = mysql_query($sql) or die(mysql_error());
        $arr= array();
        while($row = mysql_fetch_array($result)){
            $arr[] = $row;
        }
        return $arr;
        
    }
    
    
    public function getListLopMonDVHV($ma_donvihocvu){
        $sql ="select * from tbl_lopmon where ma_donvihocvu ='".$ma_donvihocvu."'";
        $result = mysql_query($sql) or die(mysql_error());
        $arr_lopmon = array();
        
        while($row = mysql_fetch_array($result)){
            $arr_lopmon[] = $this->setLopMonBLL($row);
        }
        return $arr_lopmon;
    }
    
    public function getListLopMonDonVi($ma_donvi){
        $sql ="select * from tbl_lopmon where ma_donvi ='".$ma_donvi."'";
        $result = mysql_query($sql) or die(mysql_error());
        $arr_lopmon = array();
        
        while($row = mysql_fetch_array($result)){
            $arr_lopmon[] = $this->setLopMonBLL($row);
        }
        return $arr_lopmon;
    }
    
    public function getListLopMonCa($madm_ca){
        $sql ="select * from tbl_lopmon where madm_ca ='".$madm_ca."'";
        $result = mysql_query($sql) or die(mysql_error());
        $arr_lopmon = array();
        
        while($row = mysql_fetch_array($result)){
            $arr_lopmon[] = $this->setLopMonBLL($row);
        }
        return $arr_lopmon;
    }
    
     public function getListLopMonPhong($ma_phong){
        $sql ="select * from tbl_lopmon where ma_phong ='".$ma_phong."'";
        $result = mysql_query($sql) or die(mysql_error());
        $arr_lopmon = array();
        
        while($row = mysql_fetch_array($result)){
            $arr_lopmon[] = $this->setLopMonBLL($row);
        }
        return $arr_lopmon;
    }
    
    public function getListLopMonCBGV($ma_cb){
        $sql ="select * from tbl_lopmon where ma_cbgv ='".$ma_cb."'";
        $result = mysql_query($sql) or die(mysql_error());
        $arr_lopmon = array();
        
        while($row = mysql_fetch_array($result)){
            $arr_lopmon[] = $this->setLopMonBLL($row);
        }
        return $arr_lopmon;
    }
    public function getListLopMonNguoiTao($nguoitao){
        $sql ="select * from tbl_lopmon where nguoitao ='".$nguoitao."'";
        $result = mysql_query($sql) or die(mysql_error());
        $arr_lopmon = array();
        
        while($row = mysql_fetch_array($result)){
            $arr_lopmon[] = $this->setLopMonBLL($row);
        }
        return $arr_lopmon;
    }
    
    public function getListLopMon($condition){
        $sql ="select * from tbl_lopmon ".$condition;
        $result = mysql_query($sql) or die(mysql_error());
        $arr_lopmon = array();
        
        while($row = mysql_fetch_array($result)){
            $arr_lopmon[] = $this->setLopMonBLL($row);
        }
        return $arr_lopmon;
        
        
    }
    
    public function insertLopMon($lopmon){      
           $sql ="INSERT INTO tbl_lopmon(
               ma_lopmon
              ,ten_lopmon
              ,ngaybatdau
              ,ngayketthuc
              ,ma_donvi
              ,ma_donvihocvu
              ,madm_ca
              ,ma_phong
              ,ma_cbgv
              ,ngaytao
              ,nguoitao
              ,ngayduyet
              ,nguoiduyet
              ,trangthai_hientai
            ) VALUES (
              '".$lopmon->getma_lopmon()."' 
              ,'".$lopmon->getten_lopmon()."'  
              ,'".$lopmon->getngaybatdau()."' 
              ,'".$lopmon->getngayketthuc()."' 
              ,'".$lopmon->getma_donvi()."'  
              ,'".$lopmon->getma_donvihocvu()."' 
              ,'".$lopmon->getmadm_ca()."' 
              ,'".$lopmon->getma_phong()."' 
              ,'".$lopmon->getma_cbgv()."' 
              ,'".$lopmon->getngaytao()."'  
              ,'".$lopmon->getnguoitao()."' 
              ,'".$lopmon->getngayduyet()."' 
              ,'".$lopmon->getnguoiduyet()."'  
              ,'".$lopmon->gettrangthai_hientai()."'  
            )" ;
            
            $result = mysql_query($sql) or die(mysql_error());
            return $result;
       
    }
    
    public function updateLopMon($lopmon){
        $sql ="UPDATE tbl_lopmon
            SET          
            ten_lopmon = '".$lopmon->getten_lopmon()."'
            ,ngaybatdau = '".$lopmon->getten_lopmon()."' 
            ,ngayketthuc = '".$lopmon->getngayketthuc()."'
            ,ma_donvi = '".$lopmon->getma_donvi()."'
            ,ma_donvihocvu = '".$lopmon->getma_donvihocvu()."' 
            ,madm_ca = '".$lopmon->getmadm_ca()."' 
            ,ma_phong = '".$lopmon->getma_phong()."' 
            ,ma_cbgv = '".$lopmon->getma_cbgv()."' 
            ,ngaytao = '".$lopmon->getngaytao()."' 
            ,nguoitao = '".$lopmon->getnguoitao()."' 
            ,ngayduyet = '".$lopmon->getngayduyet()."' 
            ,nguoiduyet = '".$lopmon->getnguoiduyet()."' 
            ,trangthai_hientai = '".$lopmon->getnguoiduyet()."' 
        WHERE ma_lopmon = '".$lopmon->getma_lopmon()."'";
        $result = mysql_query($sql) or die(mysql_error());
        return $result;
    }
    
    public function deleteLopMon($ma_lopmon){
        $sql="delete from tbl_lopmon where ma_lopmon ='".$ma_lopmon."'";
        $result = mysql_query($sql) or die(mysql_error());
        return $result;        
    }    
    
    function setLopMonBLL($row){
        $lopmon = new LopMon();
        $lopmon->setLopMon($row['ma_lopmon'],$row['ten_lopmon'],$row['ngaybatdau'],$row['ngayketthuc'],$row['ma_donvi'],$row['ma_phong'],
        $row['ma_donvihocvu'],$row['ma_cbgv'],$row['madm_ca'],$row['ngayduyet'],$row['ngaytao'],$row['nguoiduyet'],$row['nguoitao'],$row['trangthai_hientao']);
        
        return $lopmon;
    }
}
?>